package com.example.tally.DataBase;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.util.Log;

import com.example.tally.adapter.ChartItemAdapter;
import com.example.tally.utils.FloatUtils;

import java.math.BigDecimal;
import java.util.ArrayList;
import java.util.List;

/*
* 负责管理数据库的类
*   主要对于表当中的内容进行操作，增删改查
* */
public class DBManager {
    private static SQLiteDatabase db;
    //初始化数据库对象
    public static void initDB(Context context){
        DBOpenHelper helper = new DBOpenHelper(context);    //得到帮助类对象
        db = helper.getWritableDatabase();     //得到数据库对象
    }

//    读取数据库当中的数据，写入内存集合里   kind:表示收入或者支出
    public static List<TypeBean>getTypeList(int kind){
        List<TypeBean>list = new ArrayList<>();
        //读取typeTb表中的数据
        String sql = "select * from typeTb where kind = "+kind;
        Cursor cursor = db.rawQuery(sql, null);
        //循环读取游标内容，存储到对象当中
        while (cursor.moveToNext()){
            String typeName = cursor.getString(cursor.getColumnIndexOrThrow("typeName"));
            int imageId = cursor.getInt(cursor.getColumnIndexOrThrow("imageId"));
            int sImageId = cursor.getInt(cursor.getColumnIndexOrThrow("sImageId"));
            int kind1 = cursor.getInt(cursor.getColumnIndexOrThrow("kind"));
            int id = cursor.getInt(cursor.getColumnIndexOrThrow("id"));
            TypeBean typeBean = new TypeBean(id, typeName, imageId, sImageId, kind);
            list.add(typeBean);
        }
        return list;
    }

//    向记账表中插入一条元素
    public static void insertItemToAccountTb(AccountBean bean){
        ContentValues values = new ContentValues();
        values.put("typeName",bean.getTypeName());
        values.put("sImageId",bean.getsImageId());
        values.put("beizhu",bean.getBeizhu());
        values.put("money",bean.getMoney());
        values.put("time",bean.getTime());
        values.put("year",bean.getYear());
        values.put("month",bean.getMonth());
        values.put("day",bean.getDay());
        values.put("kind",bean.getKind());
        db.insert("accountTb",null,values);
        Log.i("一抹清茶", "insertItemToAccountTb: 完成！！");   //测试
    }

//    获取记账表中某一月的所有支出收入情况
    public static List<AccountBean>getAccountListOneMonthFromAccountTb(int year,int month){
        List<AccountBean>list  = new ArrayList<>();
        String sql = "select * from accountTb where year=? and month=? order by id desc";
        Cursor cursor = db.rawQuery(sql, new String[]{year + "", month + ""});
        //遍历符合要求的每一行数据
        while (cursor.moveToNext()){
            int id = cursor.getInt(cursor.getColumnIndexOrThrow("id"));
            String typeName = cursor.getString(cursor.getColumnIndexOrThrow("typeName"));
            String beizhu = cursor.getString(cursor.getColumnIndexOrThrow("beizhu"));
            String time = cursor.getString(cursor.getColumnIndexOrThrow("time"));
            int sImageId = cursor.getInt(cursor.getColumnIndexOrThrow("sImageId"));
            int kind = cursor.getInt(cursor.getColumnIndexOrThrow("kind"));
            int day = cursor.getInt(cursor.getColumnIndexOrThrow("day"));
            float money = cursor.getFloat(cursor.getColumnIndexOrThrow("money"));
            AccountBean accountBean = new AccountBean(id, typeName, sImageId, beizhu, money, time, year, month, day, kind);
            list.add(accountBean);
        }
        return list;
    }

//    获取记账表中某一天的所有支出收入情况
    public static List<AccountBean>getAccountListOneDayFromAccountTb(int year,int month,int day){
        List<AccountBean>list  = new ArrayList<>();
        String sql = "select * from accountTb where year=? and month=? and day=? order by id desc";
        Cursor cursor = db.rawQuery(sql, new String[]{year + "", month + "", day + ""});
        //遍历符合要求的每一行数据
        while (cursor.moveToNext()){
            int id = cursor.getInt(cursor.getColumnIndexOrThrow("id"));
            String typeName = cursor.getString(cursor.getColumnIndexOrThrow("typeName"));
            String beizhu = cursor.getString(cursor.getColumnIndexOrThrow("beizhu"));
            String time = cursor.getString(cursor.getColumnIndexOrThrow("time"));
            int sImageId = cursor.getInt(cursor.getColumnIndexOrThrow("sImageId"));
            int kind = cursor.getInt(cursor.getColumnIndexOrThrow("kind"));
            float money = cursor.getFloat(cursor.getColumnIndexOrThrow("money"));
            AccountBean accountBean = new AccountBean(id, typeName, sImageId, beizhu, money, time, year, month, day, kind);
            list.add(accountBean);
        }
        return list;
    }

//    获取某一天的支出和收入的总金额   kind:支出=0   收入=1
    public static float getSumMoneyOneDay(int year,int month,int day,int kind){
        float total = 0.0f;
        String sql = "select sum(money) from accountTb where year=? and month=? and day=? and kind=?";
        Cursor cursor = db.rawQuery(sql, new String[]{year + "", month + "", day + "", kind + ""});
        //遍历符合要求的每一行数据
        if (cursor.moveToFirst()){
            float money = cursor.getFloat(cursor.getColumnIndexOrThrow("sum(money)"));
            total = money;
        }
        return total;
    }

//    获取某一月的支出和收入的总金额   kind:支出=0   收入=1
    public static float getSumMoneyOneMonth(int year,int month,int kind){
        float total = 0.0f;
        String sql = "select sum(money) from accountTb where year=? and month=? and kind=?";
        Cursor cursor = db.rawQuery(sql, new String[]{year + "", month + "", kind + ""});
        //遍历符合要求的每一行数据
        if (cursor.moveToFirst()){
            float money = cursor.getFloat(cursor.getColumnIndexOrThrow("sum(money)"));
            total = money;
        }
        return total;
    }

//    统计某一月的支出和收入情况有多少条   kind:支出=0   收入=1
    public static int getCountItemOneMonth(int year,int month,int kind){
        int total = 0;
        String sql = "select count(money) from accountTb where year=? and month=? and kind=?";
        Cursor cursor = db.rawQuery(sql, new String[]{year + "", month + "", kind + ""});
        if (cursor.moveToFirst()) {
            int count = cursor.getInt(cursor.getColumnIndexOrThrow("count(money)"));
            total = count;
        }
        return total;
    }



//    获取某一年的支出和收入的总金额   kind:支出=0   收入=1
    public static float getSumMoneyOneYear(int year,int kind){
        float total = 0.0f;
        String sql = "select sum(money) from accountTb where year=? and kind=?";
        Cursor cursor = db.rawQuery(sql, new String[]{year + "", kind + ""});
        //遍历符合要求的每一行数据
        if (cursor.moveToFirst()){
            float money = cursor.getFloat(cursor.getColumnIndexOrThrow("sum(money)"));
            total = money;
        }
        return total;
    }

//    根据传入的id，删除accountTb表中的一条记录
    public static int deleteItemFromAccountTbById(int id){
        int i = db.delete("accountTb", "id=?", new String[]{id + ""});
        return i;
    }

//    根据备注搜索收入和支出中的记录
    public static List<AccountBean>getAccountListByRemarkFromAccountTb(String beizhu){
        List<AccountBean>list = new ArrayList<>();
        String sql = "select * from accountTb where beizhu like '%"+beizhu+"%'";
        Cursor cursor = db.rawQuery(sql,null);
        while (cursor.moveToNext()) {
            int id = cursor.getInt(cursor.getColumnIndexOrThrow("id"));
            String typeName = cursor.getString(cursor.getColumnIndexOrThrow("typeName"));
            String bz = cursor.getString(cursor.getColumnIndexOrThrow("beizhu"));
            String time = cursor.getString(cursor.getColumnIndexOrThrow("time"));
            int sImageId = cursor.getInt(cursor.getColumnIndexOrThrow("sImageId"));
            int kind = cursor.getInt(cursor.getColumnIndexOrThrow("kind"));
            float money = cursor.getFloat(cursor.getColumnIndexOrThrow("money"));
            int year = cursor.getInt(cursor.getColumnIndexOrThrow("year"));
            int month = cursor.getInt(cursor.getColumnIndexOrThrow("month"));
            int day = cursor.getInt(cursor.getColumnIndexOrThrow("day"));

            AccountBean accountBean = new AccountBean(id, typeName, sImageId, bz, money, time, year, month, day, kind);
            list.add(accountBean);
        }
        return list;
    }

//    查询记账的表当中有几个年份信息
    public static List<Integer>getYearListFromAccountTb(){
        List<Integer> list = new ArrayList<>();
        String sql = "select distinct(year) from accountTb order by year asc";
        Cursor cursor = db.rawQuery(sql,null);
        while (cursor.moveToNext()) {
            int year =cursor.getInt(cursor.getColumnIndexOrThrow("year"));
            list.add(year);
        }
        return list;
    }

//    删除accountTb表格当中的所有数据
    public static void deleteAllAccountTb(){
        String sql = "delete from accountTb";
        db.execSQL(sql);
    }

//    查询指定年份和月份的收入或支出的某一种类型的总钱数
    public static List<ChartItemBean>getChartListFromAccountTb(int year,int month,int kind){
        List<ChartItemBean>list = new ArrayList<>();
        float sumMoneyOneMonth = getSumMoneyOneMonth(year,month,kind);   //求出支出或收入的总钱数
        String sql = "select typeName,sImageId,sum(money) as total from accountTb where year=? and month=? and kind=? group by typename " +
                "order by total desc";
        Cursor cursor = db.rawQuery(sql, new String[]{year + "", month + "", kind + ""});
        while (cursor.moveToNext()) {
            int sImageId = cursor.getInt(cursor.getColumnIndexOrThrow("sImageId"));
            String typeName = cursor.getString(cursor.getColumnIndexOrThrow("typeName"));
            float total = cursor.getFloat(cursor.getColumnIndexOrThrow("total"));

            //计算所占百分比  total/sumMonth
            float ratio = FloatUtils.div(total,sumMoneyOneMonth);
            ChartItemBean bean = new ChartItemBean(sImageId, typeName, ratio, total);
            list.add(bean);
        }
        return list;
    }

//    获取某一月当中某一天收入支出最大的金额，返回金额是多少
    public static float getMaxMoneyOneDayInMonth(int year,int month,int kind){
        String sql = "select sum(money) from accountTb where year=? and month=? and kind=? group by day order by sum(money) desc";
        Cursor cursor = db.rawQuery(sql, new String[]{year + "", month + "", kind + ""});
        if (cursor.moveToFirst()) {
            float money = cursor.getFloat(cursor.getColumnIndexOrThrow("sum(money)"));
            return money;
        }
        return 0;
    }

//    根据指定月份,获取每天的收入支出的总金额的集合
    public static List<BarChartItemBean>getSumMoneyOneDayInMonth(int year,int month,int kind){
        String sql = "select day,sum(money) from accountTb where year=? and month=? and kind=? group by day";
        Cursor cursor = db.rawQuery(sql, new String[]{year + "", month + "", kind + ""});
        List<BarChartItemBean>list = new ArrayList<>();
        while (cursor.moveToNext()) {
            int day = cursor.getInt(cursor.getColumnIndexOrThrow("day"));
            float smoney = cursor.getFloat(cursor.getColumnIndexOrThrow("sum(money)"));
            BarChartItemBean itemBean = new BarChartItemBean(year, month, day, smoney);
            list.add(itemBean);
        }
        return list;
    }

}


